/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Model.BeanUltility;

import Model.DataUltility.DatabaseConnector;
import Model.ObjectBean.Resource;
import com.sun.xml.rpc.processor.modeler.j2ee.xml.resourceEnvRefType;
import java.sql.ResultSet;
import java.util.ArrayList;
import javax.servlet.http.HttpServletRequest;

/**
 *
 * @author Tran Nguyen Anh Tu
 */
public class ResourceMapper {
    
    public static void itemArrayMapping(ArrayList itemArray, ResultSet result){
        try{    
        while(result.next())
            {
              Resource item = new Resource();
              item.setRe_id(result.getString("re_id"));
              item.setGroup_id(result.getString("group_id"));
              item.setLabel(result.getString("label"));
              item.setIntroduce(result.getString("introduce"));
              item.setCover_image(result.getString("cover_image"));
              item.setSold_number(result.getString("sold_number"));
              item.setPrice(result.getString("price"));
              item.setCategory_id(result.getString("category_id"));
              item.setAuthor(result.getString("author"));
              item.setPage_num(result.getString("page_num"));
              item.setPublisher_id(result.getString("publisher_id"));
              item.setPublish_date(result.getString("publish_date"));
              item.setTranslator(result.getString("translator"));
              item.setLanguage(result.getString("language"));
              item.setDiscount(result.getString("discount"));
              item.setCountry(result.getString("country"));
              item.setStock(result.getString("stock"));             
              itemArray.add(item);
            }
        }catch(Exception e){
            System.out.println(e);
        }       
    }
        //1.Map all condition into local variables
    //2.Check through all local variables
    //  if(group||category is null or equal(*)) do nothing;
    //  else map them to a param array;
    //  (use a trick here, because null value, equal method will throw a exception, just catch it and do nothing)
    //  if(author||iname equal "") do nothing; 
    //  else map them to a param array;
    //  (because author and iname are values from text box, mean that they can't have null values)
    //3.If param array has any field, run a loop to check all field in array, add WHERE/AND/LIKE to make
    //  complete querry string. Mean that the return querry string is something like 
    //  SELECT * FROM resource WHERE <condition> [AND <condition>]
    //4.If param array doesnt have any field, the return querry string is "SELECT * FROM resource"
    private static String analyzeRequestQuerry(HttpServletRequest req){
        String group=req.getParameter("group");
        String category=req.getParameter("category");
        String author=req.getParameter("author");
        String iname=req.getParameter("iname");
        
        String querry="SELECT * FROM resource ";
        String column_label;
        String column_value;
        ArrayList<String[]> param = new ArrayList<String[]>();
        
        try{
            if(!group.equals("*")){
                param.add(new String[]{"group_id",group});
            }
        }
        catch(Exception e){}
        
        try{
           if(!category.equals("*")){
                param.add(new String[]{"category_id",category});
            }
        }
        catch(Exception e){}
        
        try{
            if(!author.equals(""))
                param.add(new String[]{"author",author});
            if(!iname.equals(""))
                param.add(new String[]{"label",iname});
        }
        catch(Exception e){}
   
        if(!param.isEmpty()) 
        {
            querry+=" WHERE ";
            System.out.println(querry+"not empty");
            for(int i =0;i<param.size();i++)
            {
                column_label=param.get(i)[0];
                column_value=param.get(i)[1];
                if(column_label.equals("author")||column_label.equals("label"))
                    querry+=column_label+" LIKE '%"+column_value+"%' ";
                else
                    querry+= column_label+"='"+column_value+"' ";
                if(i<param.size()-1) querry+=" AND ";
            }
        }
        querry+=" LIMIT 160";
        return querry;
                
    }
    public static Resource getResource(String reID)
    {
        String isInStock="";
        ArrayList result=new ArrayList();
        String querryString = "select re_id,groups.name as group_id, label,introduce,cover_image,sold_number, price, "
                            + "categories.name as category_id, author, page_num, publishers.name as publisher_id, publish_date, translator, "
                            + "language, discount, country,stock "
                            + "from resource,publishers,groups,categories "
                            + "where re_id='"+ reID+"' "
                            + "and resource.category_id=categories.category_id "
                            + "and resource.publisher_id=publishers.publisher_id "
                            + "and resource.group_id=groups.group_id ";
        itemArrayMapping(result,DatabaseConnector.getResultSet(querryString));
        Resource item = (Resource)result.get(0);
        return item;
    }
    public static ArrayList getResourceList(HttpServletRequest req)
    {
        String query=analyzeRequestQuerry(req);    
        ArrayList result = new ArrayList();
        ResultSet rs=DatabaseConnector.getResultSet(query);        
        itemArrayMapping(result, rs); 
        return result;
    }
    public static ArrayList getResourceNew(){
        ArrayList result = new ArrayList();
                //Select 8 random new items from 10 orderd new items
        try {
            String querry = "SELECT* FROM ("
                            + "SELECT * FROM resource "
                            + "WHERE group_id='BOOK' "
                            + "ORDER BY publish_date DESC "
                            + "LIMIT 10 ) temptable ORDER BY RAND() "
                            + "LIMIT 8";
            ResultSet rsNewItems = DatabaseConnector.getResultSet(querry);
            itemArrayMapping(result, rsNewItems);
        } catch (Exception e) {
            System.out.print(e);
        }
        return result;
    }
    public static ArrayList getResourceDiscount(){
        ArrayList result = new ArrayList();
         //Select 12 random items that have discount      
        try {
            String querry = "SELECT * FROM ("
                    + " SELECT * FROM resource "
                    + "WHERE discount>0 "
                    + "AND group_id='BOOK' "
                    + ") temptable ORDER BY RAND() "
                    + "LIMIT 8";
            ResultSet rsDiscount = DatabaseConnector.getResultSet(querry);            
            itemArrayMapping(result, rsDiscount);
        } catch (Exception e) {
            System.out.print(e);
        }
        return result;
    }
    public static ArrayList getResourceBestSeller(){
        ArrayList result = new ArrayList();
                //Select 8 random best selling items from 100 orderd best selling items
        try {
            String querry = "SELECT* FROM ("
                            + "SELECT * FROM resource "
                            + "WHERE group_id='BOOK' "
                            + "ORDER BY sold_number DESC "
                            + "LIMIT 100 ) temptable ORDER BY RAND() "
                            + "LIMIT 8";
            ResultSet rsTopSeller = DatabaseConnector.getResultSet(querry);
            itemArrayMapping(result, rsTopSeller);
        } catch (Exception e) {
            System.out.print(e);
        }
        return result;
    }
}
